﻿namespace BlackHole.DAO
{
    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;

    public static partial class Utility
    {
        /// <summary>
        /// get all district from db
        /// </summary>
        /// <returns>区县列表</returns>
        public static List<District> GetAllDistricts()
        {
            List<District> ret = new List<District>();

            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand("GetAllDistricts", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    District district = new District();
                    district.Id = (int)reader["Id"];
                    district.DistrictName = reader["DistrictName"].ToString();
                    ret.Add(district);
                }
            }

            return ret;
        }

        /// <summary>
        /// get all devcies from db
        /// </summary>
        /// <returns>设备列表</returns>
        public static List<Device> GetAllDevices()
        {
            List<Device> ret = new List<Device>();

            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand("GetAllDevices", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Device device = new Device();
                    device.Id = (int)reader["Id"];
                    device.DeviceName = reader["DeviceName"].ToString();
                    ret.Add(device);
                }
            }

            return ret;
        }

        /// <summary>
        /// get all faults by device
        /// </summary>
        /// <param name="deviceId">device id</param>
        /// <returns>fault list</returns>
        public static List<Fault> GetFaultsByDeviceId(int deviceId)
        {
            List<Fault> ret = new List<Fault>();

            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand("GetFaultsByDeviceId", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@deviceId", deviceId);

                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Fault fault = new Fault();
                    fault.Id = (int)reader["Id"];
                    fault.FaultName = reader["FaultName"].ToString();
                    fault.FaultCode = reader["FaultCode"].ToString();
                    ret.Add(fault);
                }
            }

            return ret;
        }

        /// <summary>
        /// get owners by districtid
        /// </summary>
        /// <param name="districtId">district id</param>
        /// <returns>owner list</returns>
        public static List<Owner> GetOwnersByDistrictId(int districtId)
        {
            List<Owner> ret = new List<Owner>();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand("GetOwnersByDistrictId", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@districtId", districtId);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Owner owner = new Owner();
                    owner.Id = (int)reader["Id"];
                    owner.OwnerName = reader["OwnerName"].ToString();
                    ret.Add(owner);
                }
            }

            return ret;
        }

        public static List<Product> GetProductsDetailBySerialNO(string serialNO, int statusId = 0)
        {
            List<Product> ret = new List<Product>();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand("GetProductsDetailBySerialNO", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@serialNO", serialNO);
                cmd.Parameters.AddWithValue("@statusId", statusId);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Product product = new Product();
                    product.Id = (int)reader["Id"];
                    product.ProductDeviceId = (int)reader["DeviceId"];
                    product.ProductDeviceName = reader["DeviceName"].ToString();
                    product.ProductDistrictName = reader["DistrictName"].ToString();
                    product.ProductOwnerName = reader["OwnerName"].ToString();
                    product.StatusId = (int)reader["StatusId"];
                    product.StatusName = Enum.GetName(typeof(ProductStatus), product.StatusId);
                    product.SerialNO = reader["SerialNO"].ToString();
                    product.PsamNO = reader["PsamNO"].ToString();
                    product.RomVersion = reader["ROMVersion"].ToString();
                    product.RepairCycle = (int)reader["RepairCycle"];
                    ret.Add(product);
                }
            }

            return ret;
        }

        public static List<Product> GetAllProductsDetails()
        {
            List<Product> products = new List<Product>();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                SqlCommand cmd = new SqlCommand("GetAllProductsDetails", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                conn.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Product p = new Product();
                    p.ProductDeviceName = reader["DeviceName"].ToString();
                    p.ProductDistrictName = reader["DistrictName"].ToString();
                    p.ProductOwnerName = reader["OwnerName"].ToString();
                    p.StatusName = reader["StatusName"].ToString();
                    p.SerialNO = reader["SerialNO"].ToString();
                    p.PsamNO = reader["PsamNO"].ToString();
                    p.RomVersion = reader["ROMVersion"].ToString();
                    p.RepairCycle = int.Parse("0" + reader["RepairCycle"].ToString());
                    products.Add(p);
                }
            }

            return products;
        }

        public static List<RepairRecord> GetRepairRecords(int realFaultId, int districtId, int ownerId, int deviceId, string inDateStart, string outDateEnd)
        {
            List<RepairRecord> records = new List<RepairRecord>();
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                string cmdStr;
                cmdStr = "SELECT d.DistrictName,o.OwnerName,s.StatusName,p.SerialNO,p.PsamNO,p.ROMVersion,dev.DeviceName,InDate,OutDate,reqf.FaultCode+reqf.FaultName RequestFaultName,reaf.FaultCode+reaf.FaultName RealFaultName,r.RepairMethod,r.RepairGear ";
                cmdStr += "FROM Records r ";
                cmdStr += "JOIN Products p ON r.ProductID=p.Id ";
                cmdStr += "JOIN Owners o ON p.OwnerId=o.Id ";
                cmdStr += "JOIN Districts d ON o.DistrictId=d.Id ";
                cmdStr += "LEFT JOIN Faults reaf ON r.RealFaultID=reaf.Id ";
                cmdStr += "LEFT JOIN Faults reqf ON r.RequestFaultID=reqf.Id ";
                cmdStr += "JOIN Statuses s ON p.StatusId=s.Id ";
                cmdStr += "JOIN Devices dev ON p.DeviceId=dev.Id ";

                cmdStr += "WHERE InDate >='" + inDateStart + "' AND InDate <='" + outDateEnd + "' AND ";

                if (realFaultId != 0) cmdStr += " r.RealFaultId=" + realFaultId.ToString() + " AND ";
                if (districtId != 0)
                {
                    if (ownerId != 0) cmdStr += "p.OwnerId=" + ownerId.ToString() + " AND ";
                    else cmdStr += "o.DistrictId=" + districtId.ToString() + " AND ";
                }

                if (deviceId != 0) cmdStr += "p.DeviceId=" + deviceId.ToString() + " AND ";
                cmdStr += "1=1";

                SqlCommand cmd = new SqlCommand(cmdStr, conn);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    RepairRecord r = new RepairRecord();
                    r.DistrictName = reader["DistrictName"].ToString();
                    r.OwnerName = reader["OwnerName"].ToString();
                    r.ProductStatus = reader["StatusName"].ToString();
                    r.ProductSerialNO = reader["SerialNO"].ToString();
                    r.ProductPsamNO = reader["PsamNo"].ToString();
                    r.ProductRomVersion = reader["RomVersion"].ToString();
                    r.ProductDeviceName = reader["DeviceName"].ToString();
                    r.RealFaultName = reader["RealFaultName"].ToString();
                    r.RequestFaultName = reader["RequestFaultName"].ToString();
                    r.InDate = reader["InDate"].ToString();
                    r.OutDate = reader["OutDate"].ToString();
                    r.RepairMethod = reader["RepairMethod"].ToString();
                    r.RepairGear = reader["RepairGear"].ToString();
                    records.Add(r);
                }

                return records;
            }
        }
    }
}